/**
 * 
 */
/**
 * @author Administrator
 *
 */
package com.yqwl.service.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xwpf.usermodel.ParagraphAlignment;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.mysql.jdbc.Connection;

import com.yqwl.common.utils.POIWordUtil;
import com.yqwl.pojo.ColumnInfo;
import com.yqwl.pojo.ColumnNameEnum;
import com.yqwl.pojo.DataDirectory;
import com.yqwl.service.DataDirectorysService;



@Service("dataDirectorysService")
@Transactional(rollbackFor = { Exception.class })
public class DataDirectorysServiceImpl implements DataDirectorysService {
	@Resource(name = "dataSource")
	public DataSource dataSource;

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	private final Logger logger = LoggerFactory.getLogger(getClass());

	@Override
	public String generateDataDirectory(DataDirectory dataDirectory,Connection conn) {
		if (dataDirectory.getTableHeader().size() == 0) {
			logger.info("改错了吧，不要随便编辑字段宽度");
			return "改错了吧，不要随便编辑字段宽度";
		}

		logger.info("阿西吧！我要放大招了");
		Map<String, String> tableCommentMap = getTableComment(dataDirectory.getDatabaseName(),conn);
		Map<String, List<String>> tableColumnsMap = getTableColumnsInfo(dataDirectory,conn);
		generatedoc(dataDirectory, tableColumnsMap, tableCommentMap);
		logger.info("正常");
		return "正常";

	}

	private String generatedoc(DataDirectory dataDirectory, Map<String, List<String>> tableMap,
			Map<String, String> tableCommentMap) {
		String databaseName = dataDirectory.getDatabaseName();
		String filePath = dataDirectory.getBaseFielPath() + databaseName + "数据库字典.docx";

		OutputStream out = null;
		try {
			XWPFDocument doc = new XWPFDocument();

			// 设置标题
			POIWordUtil.setTitle(doc, databaseName + "数据库字典", 20, ParagraphAlignment.CENTER);
			// 换行三次
			POIWordUtil.nextLine(doc, 3);

			Iterator iterators = tableMap.entrySet().iterator();
			while (iterators.hasNext()) {
				Map.Entry entry = (Map.Entry) iterators.next();
				String table_name = (String) entry.getKey();
				List<String> columnList = (List<String>) entry.getValue();

				String tableNameStr = table_name;
				if (StringUtils.isNotEmpty(tableCommentMap.get(table_name))) {
					tableNameStr += "【" + tableCommentMap.get(table_name) + "】";
				}
				// 插入表名
				POIWordUtil.newParagraphText(doc, tableNameStr);
				POIWordUtil.nextLine(doc, 0);
			}
			Iterator iterator = tableMap.entrySet().iterator();
			while (iterator.hasNext()) {
				Map.Entry entry = (Map.Entry) iterator.next();
				String table_name = (String) entry.getKey();
				List<String> columnList = (List<String>) entry.getValue();

				String tableNameStr = table_name;
				if (StringUtils.isNotEmpty(tableCommentMap.get(table_name))) {
					tableNameStr += "【" + tableCommentMap.get(table_name) + "】";
				}
				// 插入表名
				POIWordUtil.newParagraphText(doc, tableNameStr);

				// 创建指定行列的表格
				XWPFTable table = POIWordUtil.createTable(doc, columnList.size() + 1,
						dataDirectory.getTableHeader().size());

				// 设置表头，列宽
				XWPFTableRow rowHeader = table.getRow(0);
				Iterator headeriterator = dataDirectory.getTableHeader().entrySet().iterator();
				
				for (int i = 0; headeriterator.hasNext(); i++) {
					Map.Entry headerEntry = (Map.Entry) headeriterator.next();
			
					ColumnInfo columnInfo = (ColumnInfo) headerEntry.getValue();

					String width = (int) (9000 * ((double) columnInfo.getWidthPercent() / 90)) + "";

					setTableCell(rowHeader, i, columnInfo.getDesc(), width);
				}

				// 插入单元格文本
				for (int i = 0; i < columnList.size(); i++) {
					String[] columninfos = columnList.get(i).split("@");
					XWPFTableRow row = table.getRow(i + 1);
					for (int j = 0; j < columninfos.length; j++) {
						setTableCell(row, j, columninfos[j], "0");
					}
				}
				POIWordUtil.nextLine(doc, 1);
			}
			logger.info("你好有缘人，快去你自己设置的路径看看吧！！！");
			out = new FileOutputStream(new File(filePath));
			doc.write(out);
			return "";
		} catch (Exception e) {
			logger.error("进程无法访问", e);
			return "另一个程序可能正在使用此文件，进程无法访问";
		}
	}

	public static void setTableCell(XWPFTableRow row, int CellPos, String text, String width) {
		XWPFTableCell cell = row.getCell(CellPos);
		POIWordUtil.setTableCellTextCenter(cell);
		if (!"0".equals(width)) {
			POIWordUtil.setTableCellWidth(cell, width);

		}
		cell.setText(text);
	}

	@Override
	public Map<String, List<String>> getTableColumnsInfo(DataDirectory dataDirectory,Connection conn) {
		Map<String, List<String>> map = new LinkedHashMap<String, List<String>>();

		String databaseName = dataDirectory.getDatabaseName();
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select table_name from information_schema.Tables where table_schema=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, databaseName);
			rs = ps.executeQuery();
			while (rs.next()) {
				String table_name = rs.getString("table_name");
				map.put(table_name, getColumnsInfo(dataDirectory, table_name,conn));
			}
		} catch (SQLException e) {
			logger.error("get connection error!", e);
		} finally {
			freeConnection(ps, conn);
		}
		return map;
	}

	@Override
	public List<String> getColumnsInfo(DataDirectory dataDirectory, String table_name,Connection conn) {
		List<String> list = new ArrayList<String>();

		String databaseName = dataDirectory.getDatabaseName();
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select " + dataDirectory.getColumnSql()
				+ " from information_schema.COLUMNS where table_schema=? and table_name=?";

		try {
			
			ps = conn.prepareStatement(sql);
			ps.setString(1, databaseName);
			ps.setString(2, table_name);
			rs = ps.executeQuery();
			while (rs.next()) {
				StringBuffer sb = new StringBuffer("");
				Iterator<ColumnNameEnum> iterator = dataDirectory.getTableHeader().keySet().iterator();
				while (iterator.hasNext()) {
					sb.append(isEmptyReplaceStr(rs.getString(iterator.next().name()), "-")).append("@");
				}
				list.add(sb.delete(sb.length() - 1, sb.length()).toString());
			}
		} catch (SQLException e) {
			logger.error("get connection error!", e);
		}
		return list;
	}

	/**
	 * 获取表的注释
	 *
	 * @param databaeName
	 * @return
	 */
	public Map<String, String> getTableComment(String databaeName,Connection conn) {
		Map<String, String> tableCommentMap = new HashMap<String, String>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select table_name, table_comment " + "from information_schema.TABLES where table_schema=?";
		try {
			//conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, databaeName);
			rs = ps.executeQuery();
			while (rs.next()) {
				String table_name = isEmptyReplaceStr(rs.getString("table_name"), "");
				String table_comment = isEmptyReplaceStr(rs.getString("table_comment"), "");
				tableCommentMap.put(table_name, table_comment);
			}
		} catch (SQLException e) {
			logger.error("get connection error!", e);
		
		}
		return tableCommentMap;
	}

	private String isEmptyReplaceStr(String str, String replaceStr) {
		if (StringUtils.isEmpty(str)) {
			return replaceStr;
		}
		return str;
	}

	private void freeConnection(PreparedStatement ps, Connection conn) {
		try {
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			logger.error("close connection error!", e);
		}
	}

	
}